package com.priox.sigar.infraestructura.persistence.jpa.sigar;

import com.googlecode.ehcache.annotations.Cacheable;
import com.googlecode.genericdao.search.Search;
import com.mm.support.jpa.BaseDAO;
import com.priox.sigar.client.dto.AcreedorDTO;
import com.priox.sigar.client.dto.AcreedorSociedadDTO;
import com.priox.sigar.client.dto.ClienteSociedadDTO;
import com.priox.sigar.client.dto.SociedadDTO;
import com.priox.sigar.client.flt.AcreedorFlt;
import com.priox.sigar.domain.model.Acreedor;
import com.priox.sigar.domain.model.AcreedorRepository;
import com.priox.sigar.domain.model.AcreedorSociedad;
import com.priox.sigar.server.infrastructure.persistence.util.WhereParams;
import com.priox.sigar.util.TimeObserver;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;


/**
 * User: aglwkrs
 * Date: 30/11/11
 */
@Repository
public class AcreedorRepositoryImpl extends BaseDAO<Acreedor, String> implements AcreedorRepository {

    private final static Logger logger = LoggerFactory.getLogger(AcreedorRepositoryImpl.class);

    private NamedParameterJdbcTemplate parameterJdbcTemplate;
    private JdbcTemplate jdbcTemplate;


    @Autowired
    DataSource dataSource;

    @PostConstruct
    public void init() {
        this.parameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List<Acreedor> findByIds(List<String> ids) {
        if(ids.isEmpty()) return new ArrayList<Acreedor>();
        Search s = new Search();
        s.addFilterIn("id", ids);
        return search(s);
    }

    public List<Acreedor> findByNameList(String name) {
        Search s = new Search();
        if (StringUtils.isNotBlank(name)) {
            s.addFilterILike("nombre", name.toUpperCase() + "%");
        }
        s.addSortAsc("nombre");
        return search(s);


    }

    public List<AcreedorDTO> findByFilter(AcreedorFlt filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  ");
        sql.append("       ta.id       as  \"id\"   , ");
        sql.append("       ta.codigo   as  \"codigo\"    , ");
        sql.append("       ta.razonSocial as  \"razonSocial\" , ");
        sql.append("       ta.fechaCreacion as  \"fechaCreacion\" ");
        sql.append("FROM   ");
        sql.append("       tm_acreedor ta  ");
        sql.append("WHERE  1=1 ");
        WhereParams params = new WhereParams();
//        sql.append(params.filter(" and ta.codigo  = :codigo ", filter.getCodigo()));
        if(StringUtils.isNotBlank(filter.getRazonSocial())){
            sql.append(params.filter(" and ta.razonSocial like :razonSocial ", "%"+filter.getRazonSocial()));
        }
        sql.append(" order by 1,2,3");

        return parameterJdbcTemplate.query(sql.toString(), params.getParams(), new BeanPropertyRowMapper<AcreedorDTO>(AcreedorDTO.class));

    }



    public void saveCadena(Acreedor cadena) {
        this.persist(cadena);
    }


    public Acreedor findBy(String code) {
        System.out.println("buscanr <"+code+">");
        Search s = new Search();
        if (code != null) {
            s.addFilterEqual("id", code);
        }
        return searchUnique(s);
    }

    public List<Acreedor> findByRUC(String ruc) {
        Search s = new Search();
        if (ruc != null) {
            s.addFilterEqual("codigo", ruc);
        }

        return search(s);
    }

    @Override
    public void persisSociedades(List<AcreedorSociedad> acreedorSociedadList) {
        for (Iterator<AcreedorSociedad> iterator = acreedorSociedadList.iterator(); iterator.hasNext(); ) {
            AcreedorSociedad next = iterator.next();
            em().persist(next);
        }

    }

    public void delete(Long acreeedorId) {
        jdbcTemplate.update("delete from TR_ACREEDOR_SOCIEDAD where acreedor_id = ? ",
                new Object[]{acreeedorId});

        jdbcTemplate.update("delete from TM_ACREEDOR where id = ? ",
                new Object[]{acreeedorId});

    }

    public void saveAcreedorSociedad(final Long acreeedorId,final List<SociedadDTO> sociedades) {
        jdbcTemplate.update("delete from TR_ACREEDOR_SOCIEDAD where acreedor_id = ? ",
                new Object[]{acreeedorId});

        String sql = "insert into TR_ACREEDOR_SOCIEDAD (acreedor_id ,sociedad_id) values (?,?)";

        TimeObserver timeObserver = new TimeObserver("insert del bloque en ");
        timeObserver.empezar();
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            public void setValues(PreparedStatement ps, int i) throws SQLException {
                SociedadDTO customer = sociedades.get(i);
                ps.setLong(1, acreeedorId);
                ps.setString(2, customer.getSociedadId());
            }
            public int getBatchSize() {
                return sociedades.size();
            }
        });
        timeObserver.terminar();


    }

    public List<AcreedorSociedadDTO> obtenerAcreedorSociedadList(String acreedorId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT   ");
        sql.append("clientesoc0_.ACREEDOR_ID           AS \"acreedorId\",   ");
        sql.append("clientesoc0_.SOCIEDAD_ID          AS \"sociedadId\",   ");
        sql.append("clientesoc0_.CREADO_POR           AS \"creadoPor\",   ");
        sql.append("clientesoc0_.FECHA_CREACION       AS \"fechaCreacion\",   ");
        sql.append("clientesoc0_.FECHA_MODIFICACION   AS \"fechaModificacion\",   ");
        sql.append("clientesoc0_.MODIFICADO_POR       AS \"modificadoPor\",   ");
        sql.append("ISNULL(clientesoc0_.RELACIONADO,0)   AS \"relacionado\",   ");
        sql.append("sociedad1_.GRUPO_EMPRESARIAL_ID   AS \"grupoEmpresarialId\",   ");
        sql.append("sociedad1_.NOMBRE                 AS \"sociedadNombre\",   ");
        sql.append("grupoempre2_.NOMBRE               AS \"grupoEmpresarialNombre\"   ");
        sql.append("FROM   ");
        sql.append("SGTR_ACREEDOR_SOCIEDAD clientesoc0_   ");
        sql.append("LEFT OUTER JOIN SGTM_SOCIEDAD sociedad1_   ");
        sql.append("ON clientesoc0_.SOCIEDAD_ID=sociedad1_.SOCIEDAD_ID   ");
        sql.append("LEFT OUTER JOIN SGTM_GRUPO_EMPRESARIAL grupoempre2_   ");
        sql.append("ON sociedad1_.GRUPO_EMPRESARIAL_ID=grupoempre2_.GRUPO_EMPRESARIAL_ID   ");
        sql.append("WHERE   ");
        WhereParams params = new WhereParams();
        sql.append(params.filter(" clientesoc0_.ACREEDOR_ID= :acreedorId   ", acreedorId));

        return parameterJdbcTemplate.query(sql.toString(), params.getParams(), new BeanPropertyRowMapper<AcreedorSociedadDTO>(AcreedorSociedadDTO.class));

    }


    public void executeSP() {
        UpdateAcreedorSociedad storeProcedure= new UpdateAcreedorSociedad(jdbcTemplate);
        storeProcedure.execute();
    }

    private class UpdateAcreedorSociedad extends StoredProcedure
    {
        public UpdateAcreedorSociedad(JdbcTemplate template) {
            super(template, "SP_UPDATE_ACREEDOR_SOCIEDAD");
            setFunction(false);
            compile();
        }

        public void execute()
        {
            HashMap input = new HashMap();
            execute(input);
        }

    }


}
